select * from tblCXCRMAgentLeads Where CustomerID = 56407

select * from tblCarDetailTH Where CarID = 89313

select top 10 * from tblContactUser
 Where ProductID = 89313
 Order By CreatedDate DESc

----CHECK
Select l.AgentLeadID, l.CreatedDate LeadCreatedDate, l.AgentID, u.Email, u.Info1, r.LogId, r.CarId, r.RefCEUserID, 
r.CreatedQuoteDate, r.CreatedDate, r.ModifiedDate
FROM tblCXCRMAgentLeads l 
INNER JOIN tblRerunQuoteMail_Log r ON l.CustomerID = r.RefCEUserID And l.CarID = r.CarID
INNER JOIN tblCEUser u ON l.AgentID = u.CEID
Where l.[Status] = 1 And r.ModifiedDate IS NOT NULL
Order By r.ModifiedDate DESC

Select Count(l.AgentLeadId)
FROM tblCXCRMAgentLeads l 
INNER JOIN tblRerunQuoteMail_Log r ON l.CustomerID = r.RefCEUserID And l.CarID = r.CarID
Where Convert(varchar(8), l.CreatedDate, 112) Between '20130729' And '20130730'
		
select * from tblRerunQuoteMail_Log Where ModifiedDate IS NOT NULL Order By ModifiedDate DESC 


select top 10 * from tblCarPolicy Where Len(Info7) > 5

Open Symmetric Key CXUserSym
Decryption By Certificate CXUserCert
	
SELECT l.[LogId]
      ,l.[RefCEUserId]
      ,l.[CarID]
      ,l.[RowGuid]
      ,c.Info5 AS Email
      ,c.Info6 AS Phone
      --,c.Info7 AS Firstname
      --,c.Info8 AS Lastname
	  ,CONVERT(varchar(100),DECRYPTBYKEY(p.EInfo1)) AS FirstName
      ,CONVERT(varchar(100),DECRYPTBYKEY(p.EInfo2)) AS Lastname  
      ,l.[CreatedQuoteDate]
      ,l.Lang
	  ,IsSendEmail
  FROM [dbo].[tblRerunQuoteMail_Log] l 
  INNER JOIN tblCarDetailTH c ON l.CarID = c.CarID And l.RefCEUserID = c.RefCEUserID
  INNER JOIN tblCEUsrProfile p ON l.RefCEUserId = p.RefCEUserId
  WHERE (CONVERT(varchar(10),l.CreatedDate,110) Between CONVERT(varchar(10), GETDATE() - 5 ,110) And CONVERT(varchar(10), GETDATE(),110))
  --And l.RefCEUserID NOT IN (Select CustomerID from tblCXCRMAgentLeads Where Convert(varchar(8), CreatedDate, 112) BETWEEN '20130530' And '20130729')
  --And c.Info5 Like 'actiontisut@gmail.com'
  --And l.LogId >= 290
  --AND c.Info5 IN ('tumwan34@yahoo.com', 'monticha.sa@gmail.com', 'may_ju_jub@hotmail.com', 'supersus2@hotmail.com', 'rsombat@gmail.com', 'mavikas@hotmail.com', 'bee117@hotmail.com', 'lucknaka_2@hotmail.com', 'noppadol.cha@egat.co.th')

  --Update [tblRerunQuoteMail_Log] SET IsNoQuoteResult = 1
  --Where CarID NOT IN (Select distinct CarID from tblCarInsuranceTempResultTH Where (CONVERT(varchar(10),ModifiedDate,110) Between CONVERT(varchar(10), GETDATE() - 5 ,110) And CONVERT(varchar(10), GETDATE(),110)))
  --Where LogId Between 352 And 360

  CLOSE Symmetric Key CXUserSym

  
	--Update tblCarPolicy 
	--SET CoverageCommenceDate = DateAdd(year, 1, CoverageCommenceDate), ModifiedDate = GETDATE() 
	--Where CECarID IN (Select CarID from tblRerunQuoteMail_Log Where (IsSendEmail = 1 OR IsNoQuoteResult = 1))
	--And CoverageCommenceDate < GetDate()
	select CoverageCommenceDate, CreatedDate, ModifiedDate from tblCarPolicy
	Where CECarID IN (Select CarID from tblRerunQuoteMail_Log Where (IsSendEmail = 1 OR IsNoQuoteResult = 1))
	
  SELECT distinct c.RefCEUserId, CASE WHEN (c.Info7 NOT Like '%[A-Z,a-z]%') OR (up.NInfo7 = 1) OR (up.NInfo7 is null) THEN 'th'
		ELSE 'en' END AS LangOption, Max(c.CarID) CarId, u.RowGuid, Max(c.CreatedDate)
		--c.Info5 AS Email, u.RowGuid,c.Info6 AS Phone,c.Info7 AS Firstname,c.Info8 AS Lastname, 
		
		FROM tblCarDetailTH c 
		INNER JOIN tblCarPolicy cp ON c.CarID = cp.CECarID And c.RefCEUserID = cp.RefCEUserID
		INNER JOIN tblCEUsrProfile up ON c.RefCEUserID = up.RefCEUserID
		INNER JOIN tblCEUser u ON u.CEID = up.RefCEUserID
		INNER JOIN [tblUserResult] ur ON c.CarID = ur.[ID1]
		WHERE 
		c.RefCEUserId NOT IN (Select RefCEUserId from [tblRerunQuoteMail_Log] WHERE (CONVERT(varchar(8),CreatedDate,112) Between CONVERT(varchar(8), GETDATE() - 5 ,112) And CONVERT(varchar(8), GETDATE(),112)))
		AND (c.RefCEUserId IS NOT NULL AND c.RefCEUserId > 0) AND (u.IsReject IS NULL OR u.IsReject = 0) And u.UsrTypeId = 400 AND (u.UserID = 'CEUSERAUTOIDCI' OR u.UserID = 'CEUSERAUTOIDMOBILECI')
		AND c.RefCEUserId NOT IN (Select l.CustomerID from tblCXCRMAgentLeads l Where (Convert(varchar(8), l.CreatedDate, 112) Between Convert(varchar(8), GETDATE() - 60, 112) And Convert(varchar(8), GETDATE(), 112)))
		AND CONVERT(varchar(8),c.CreatedDate,112) BETWEEN CONVERT(varchar(8), DATEADD(YEAR, -1, DATEADD(DAY,14,GETDATE())),112) AND CONVERT(varchar(8), DATEADD(YEAR, -1, DATEADD(DAY,14 + 7,GETDATE())),112)
	    AND ((c.Info8 NOT Like '%[A-Z,a-z]%' AND (LEN(c.Info7) = 0 OR c.Info7 NOT Like '%[A-Z,a-z]%')) OR (up.NInfo7 = 1 OR up.NInfo7 = 0))
		And c.RefCEUserId > 0 AND ur.IsComplete = 0
		And u.CEID NOT IN (select distinct RefCEUserID  from tblNonGGCarMoreDetails)
		AND u.CEID NOT IN (select distinct CustomerID from tblCXCRMCallStatus Where MainCallStatusTypeID = 6)
		GROUP BY c.RefCEUserId, c.Info7, up.NInfo7, u.RowGuid
		Order By c.RefCEUserId


		select RefCeUserID from [tblRerunQuoteMail_Log] Where RefCeUserID NOT IN (Select CustomerID from tblCXCRMAgentLeads Where Convert(varchar(8), CreatedDate, 112) Between '20130729' and '20130730')
		And (IsSendEmail = 1 OR IsNoQuoteResult = 1)
		---*****************************************************OTHERS*****************************************************
		--		Declare @CommenceDateStart smallint
		--Declare @CommenceDateEnd smallint
		--Declare @OldStartDate varchar(8)
		--Declare @OldEndDate varchar(8)
		--Declare @LeadStartDate varchar(8)
		--Declare @LeadEndDate varchar(8)
		--Declare @QuoteDateStart smallint
		--Declare @QuoteDateEnd smallint

		--Select
		--		@QuoteDateStart = QuoteStart, 
		--		@QuoteDateEnd = QuoteEnd, 
		--		@CommenceDateStart = CommDateStart, 
		--		@CommenceDateEnd = CommDateEnd
				
		--From tblCXCRMLeadSetting Where [Status] = 1 And SettingID = 1


		--Declare @StartCommDate date
		--Declare @EndCommDate date

		--SET @StartCommDate = Convert(varchar(8), DateAdd(year, -1, GetDate()) + (@CommenceDateStart + 14), 112)
		--SET @EndCommDate = Convert(varchar(8), DateAdd(year, -1, GetDate()) + (@CommenceDateEnd + 14), 112)

		--SET  @LeadStartDate = Convert(varchar(8), DateAdd(day, -60, GetDate()), 112)
		--SET  @LeadEndDate = Convert(varchar(8), GetDate(), 112)

		--SET @OldStartDate = Convert(varchar(8), DateAdd(year, -1, GetDate() + 14), 112)
		--SET @OldEndDate = Convert(varchar(8), DateAdd(year, -1, GetDate()) + (@QuoteDateStart + 14), 112)

		--Select @CommenceDateStart CommenceDateStart, @CommenceDateEnd CommenceDateEnd, @LeadStartDate LeadStartDate, @LeadEndDate LeadEndDate, @OldStartDate OldStartDate, @OldEndDate OldEndDate

	

		----Select r.RefCEUserID AS CEID, CASE Lang WHEN 'th' THEN 1 ELSE 0 END IsENCustomer, r.CarID,
		----r.ModifiedDate, r.CreatedQuoteDate, r.ModifiedDate
		----FROM [tblRerunQuoteMail_Log] r
		----WHERE r.RefCEUserId NOT IN (Select distinct CustomerID from tblCXCRMAgentLeads Where (Convert(varchar(8), CreatedDate, 112) Between @LeadStartDate And @LeadEndDate))
		----UNION
		----Select TOP 20
		----u.CEID, 
		----up.NInfo7 AS IsENCustomer,
		----Max(cd.CarID) CarID,
		----Max(cd.CreatedDate),
		----Max(cd.ModifiedDate)
		----FROM tblCEUser u 
		----INNER JOIN tblCEUsrProfile up ON u.CEID = up.RefCEUserID
		----INNER JOIN tblCarDetailTH cd ON u.CEID = cd.RefCEUserID
		----INNER JOIN tblCarPolicy cp ON u.CEID = cp.RefCEUserID
		----INNER JOIN tblRerunQuoteMail_Log r ON u.CEID = r.RefCEUserId
		----Where u.UsrTypeID = 400
		----AND cd.CarID > 0 AND u.CEID > 0
		----And (Convert(varchar(8), u.CreatedDate, 112) Between @OldStartDate And @OldEndDate)
		----AND (Convert(varchar(8), cp.CoverageCommenceDate, 112) Between @StartCommDate And @EndCommDate)
		----AND ((cd.Info8 NOT Like '%[A-Z,a-z]%' AND (LEN(cd.Info7) = 0 OR cd.Info7 NOT Like '%[A-Z,a-z]%')) OR (up.NInfo7 = 1 OR up.NInfo7 = 0))
		----And u.CEID NOT IN (Select distinct CustomerID from tblCXCRMAgentLeads Where (Convert(varchar(8), CreatedDate, 112) Between @LeadStartDate And @LeadEndDate))
		----And u.CEID NOT IN (select distinct RefCEUserID  from tblNonGGCarMoreDetails)
		----AND u.CEID NOT IN (select distinct CustomerID from tblCXCRMCallStatus Where MainCallStatusTypeID = 6)
		----Group By u.CEID, up.NInfo7

		--SELECT 
		--r.RefCEUserID, 
		--CASE Lang WHEN 'th' THEN 1 ELSE 0 END IsENCustomer, r.ModifiedDate,
		--r.CarID
		--FROM [tblRerunQuoteMail_Log] r
		--WHERE r.RefCEUserId NOT IN (Select distinct CustomerID from tblCXCRMAgentLeads Where (Convert(varchar(8), CreatedDate, 112) Between @LeadStartDate And @LeadEndDate))
		--Order By ModifiedDate DESC, CreatedQuoteDate ASC




		----select top 10 * from [tblRerunQuoteMail_Log] Order By ModifiedDate DESC, CreatedQuoteDate ASC
		
		----AND CONVERT(varchar(10),r.CreatedDate,110) BETWEEN CONVERT(varchar(10), DATEADD(YEAR, -1, DATEADD(DAY,@MinusDay,GETDATE())),110) AND CONVERT(varchar(10), DATEADD(YEAR, -1, DATEADD(DAY,@MinusDay + 7,GETDATE())),110)


		----AND CONVERT(varchar(10),c.CreatedDate,110) BETWEEN CONVERT(varchar(10), DATEADD(YEAR, -1, DATEADD(DAY,@MinusDay,GETDATE())),110) AND CONVERT(varchar(10), DATEADD(YEAR, -1, DATEADD(DAY,@MinusDay + 7,GETDATE())),110)